package com.health.userbackstage.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.health.userbackstage.pojo.*;
import com.health.userbackstage.service.MemberService;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface MemberMapper extends BaseMapper<Member> {

    @Select("select * from member where fileNumber=#{member.fileNumber}")
    Member selectOneByFileNumber(@Param("member") Member member);
    @Select("select * from member where name=#{member.name}")
    Member selectOneByName(@Param("member") Member member);

    /**
     * 查询member对应的order数据
     */
    @Select("SELECT o.id,o.orderdate\n" +
            "FROM `order` o,member m\n" +
            "WHERE o.member_id = m.`id` AND o.member_id = #{memberId}")
    List<Order> selectByMemberId(Long memberId);

    /**
     * 查询member对应的report数据
     */
    @Select("SELECT o.id\n" +
            "FROM `report` o,member m\n" +
            "WHERE o.member_id = m.`id` AND o.member_id = #{memberId}")
    List<Report> selectReportByMemberId(Long memberId);

    @Delete("delete from report where id = #{id}")
    int deleteReportById(Integer id);

    /**
     * 今天的日期
     * @return
     */
    @Select("SELECT CURDATE() today")
    String today();

    /**
     * 今天新增的会员数
     * @return
     */
    @Select("select count(*) from member where regTime=CURDATE()")
    long todayNewMember();

    /**
     * 本周新增的会员数
     * @return
     */
    @Select("select count(*) from member where regTime between SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1) and SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7)")
    long weekNewMember();

    /**
     * 本月新增的会员数
     */
    @Select("select count(*) from member where regTime between DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY) and LAST_DAY(CURDATE())")
    long monthNewMember();

    /**
     * 总会员数
     */
    @Select("select count(*) from member")
    long memberCount();

    /**
     * 今日预约数
     */
    @Select("SELECT COUNT(*) todayOrderNumber\n" +
            "FROM `order`\n" +
            "WHERE orderdate = CURDATE();")
    long todayOrderNumber();

    /**
     * 今日到诊数
     */
    @Select("SELECT COUNT(*) todayVisitsNumber\n" +
            "FROM `order`\n" +
            "WHERE orderdate = CURDATE() AND orderstate='到诊' ")
    long todayVisitsNumber();

    /**
     * 本周预约数
     */
    @Select("SELECT COUNT(*) thisWeekOrderNumber\n" +
            "FROM `order`\n" +
            "WHERE orderdate BETWEEN SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1) AND SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7)")
    long thisWeekOrderNumber();

    /**
     * 本周到诊数
     */
    @Select("SELECT COUNT(*) thisWeekOrderNumber\n" +
            "FROM `order`\n" +
            "WHERE orderstate = '到诊' AND orderdate BETWEEN SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-1) AND SUBDATE(CURDATE(),DATE_FORMAT(CURDATE(),'%w')-7)")
    long thisWeekVisitsNumber();

    /**
     * 本月预约数
     */
    @Select("SELECT COUNT(*) thisMonthOrderNumber\n" +
            "FROM `order`\n" +
            "WHERE orderdate BETWEEN DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY) AND LAST_DAY(CURDATE())")
    long thisMonthOrderNumber();

    /**
     * 本月到诊数
     */
    @Select("SELECT COUNT(*) thisMonthOrderNumber\n" +
            "FROM `order`\n" +
            "WHERE orderstate = '到诊' AND orderdate BETWEEN DATE_ADD(CURDATE(),INTERVAL -DAY(CURDATE())+1 DAY) AND LAST_DAY(CURDATE())\n")
    long thisMonthVisitsNumber();

    /**
     * 热门套餐的套餐名称、预约数量、占比
     */
    @Select("SELECT s.name AS NAME, COUNT(o.id) AS setmeal_count, \n" +
            "       CONCAT(ROUND( COUNT(o.id) * 100 / (SELECT COUNT(*) FROM `order`) , 1),'%' ) AS proportion\n" +
            "FROM `setmeal` s\n" +
            "LEFT JOIN `order` o ON s.id = o.setmeal_id\n" +
            "GROUP BY s.name;")
    List<HotSetmeal> hotSetmeal();

    Member findNameById(Long id);
}
